package fsmi.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import fsmi.daoservice.HomeworkDAO;
import fsmi.model.HomeworkBean;
import fsmi.model.HomeworkListBean;

public class HomeworkDB implements HomeworkDAO {
	
	private DBPool pool;
	private Connection conn;
	private PreparedStatement stmt;
	
	public HomeworkDB(){
		pool=DBPool.getInstance();
		if(pool.getConnCount()!=0){
			conn=pool.getConn();			
		}
	}
	

	@Override
	public HomeworkListBean getHomeworkList(String stuID,String cid) {
		//create homework list
		HomeworkListBean hlist=new HomeworkListBean();
		//get conn of db
		conn=pool.getConn();
		//get committed list
		String sql="select h.hid,h.hname,h.deadline,c.grade from homework h,commitment c "
				+ "where c.sid=? and h.cid=? and c.hid=h.hid and c.grade>=60;";
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, stuID);
			stmt.setString(2, cid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ResultSet result=null;
		try {
			result=stmt.executeQuery();
			while(result.next()){
				String hid=result.getString("hid");
				String hname=result.getString("hname");
				Date deadline=result.getDate("deadline");
				int grade=result.getInt("grade");
				HomeworkBean homework=new HomeworkBean();
				homework.setCid(cid);
				homework.setHid(hid);
				homework.setHname(hname);
				homework.setDeadline(deadline);
				homework.setGrade(grade);
				hlist.addCommitted(homework);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//get uncommitted list
		sql="select distinct h.hid,h.hname,h.deadline from homework h,commitment c "
				+ "where h.hid not in(select c2.hid from commitment c2 where c2.sid=?) and h.cid=?;";
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, stuID);
			stmt.setString(2, cid);
			result=stmt.executeQuery();
			while(result.next()){
				String hid=result.getString("hid");
				String hname=result.getString("hname");
				Date deadline=result.getDate("deadline");
				int grade=0;
				HomeworkBean homework=new HomeworkBean();
				homework.setCid(cid);
				homework.setHid(hid);
				homework.setHname(hname);
				homework.setDeadline(deadline);
				homework.setGrade(grade);
				hlist.addUnCommitted(homework);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//get failed list
		sql="select h.hid,h.hname,h.deadline,c.grade from homework h,commitment c "
				+ "where c.sid=? and h.cid=? and c.hid=h.hid and c.grade<60;";
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, stuID);
			stmt.setString(2, cid);
			result=stmt.executeQuery();
			while(result.next()){
				String hid=result.getString("hid");
				String hname=result.getString("hname");
				Date deadline=result.getDate("deadline");
				int grade=result.getInt("grade");
				HomeworkBean homework=new HomeworkBean();
				homework.setCid(cid);
				homework.setHid(hid);
				homework.setHname(hname);
				homework.setDeadline(deadline);
				homework.setGrade(grade);
				hlist.addFailed(homework);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//return conn to poll
		pool.returnConn(conn);
		return hlist;
	}
	
	public void destroyHomeworkDB() {
		try {
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		stmt = null;
		pool.returnConn(conn);
		conn = null;
		pool = null;
	}


}
